InterBase ODBC Driver Contents

Introduction

Windows System Requirements

Configuring an InterBase Data Source

Connecting to InterBase Using a Logon Dialog Box

Connecting to InterBase Using a Connection String

Attributes

Transaction Isolation Levels

Data Types

Using Microsoft Access or Visual Basic


InterBase: Introduction
Introduction

The InterBase ODBC driver supports connection to an InterBase server via the Open Database Connectivity Standard. The Borland InterBase ODBC driver is called BLINT04.DLL.

The InterBase driver provides the following features:

Core, Level 1, and Level 2 API functions listed as supported ODBC functions in Appendix A of The InterBase Windows Client User's Guide. In addition, the SQLDrivers, SQLExtendedFetch, SQLProcedures, SQLPrimaryKeys, and SQLTablePrivileges Level 2 functions are supported.

Minimum SQL grammar.

The following transaction isolation levels: level 1, READ cOMMITTED  (the default), level 3, SNAPSHOT, and level 3A, SNAPSHOT TABLE STABILITY.

Page-level locking.

Multiple connections and multiple statements per connection.


InterBase: System Requirements
Windows System Requirements

System requirements are:

InterBase 4.0 must be installed on a server machine.

The InterBase Client for Windows must be installed on the client PC. The InterBase Windows Client uses communications protocols such as TCP/IP to connect to servers. For more information, see the Windows Client User's Guide.

If you attempt to configure a data source and you do not have the INTERBAS directory on your path or the driver DLLs in your Windows \SYSTEM directory, the following message appears:

The setup routines for the InterBase ODBC driver could not be loaded. You may be low on memory and need to quit a few applications.


InterBase ODBC Setup
Configuring an InterBase Data Source

To configure a InterBase data source, do the following:

1.	Start the ODBC Administrator. A list of data sources appears.

2.	If you are configuring a new data source, click Add. A list of installed drivers appears. Select Borland InterBase, and click OK.

If you are configuring an existing data source, select the data source name and click Setup. The Setup dialog box appears.

3.	Specify values as follows:

Item	Description

Data Source Name	Identifies a single connection to an InterBase database system. This can be any string. Examples include Accounting or InterBase-Serv1.

Description	An optional long description of a data source name. For example, My Accounting Database or InterBase on Server #1.

Database Name	The database name to which you want to connect.

Default Logon ID	The default user name used to connect to your InterBase server. Your ODBC application may override this value or you may override this value in the Logon dialog box. The Logon ID is not case sensitive.


Connecting to InterBase Using a Logon Dialog Box

Some ODBC applications display a Logon dialog box when you are connecting to a data source. In this dialog box, enter the values as described below. Then click OK to log on to the specified InterBase server, connect to the specified database, and update the values in ODBC.INI.

For...	Enter...

Server Name	The name of the server with the InterBase database you want to access.  Click on the arrow to the right of the box to select a server name you specified in the Setup dialog box.

Database Name	The database name of the InterBase database you want to access.

User Name	Your User Name (31 characters maximum, not case-sensitive).

Password	Your password for the system (8 significant characters, case-sensitive).


Connecting to InterBase Using a Connection String

Some applications (such as Visual Basic 3.0) require a connection string to connect to a data source. A connection string must specify the data source name that tells the driver which section of ODBC.INI to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. Attributes specify items such as datasource name (DSN), logon ID (UID), password (PWD), and so on.

You can specify either long or short names in the connection string. The connection string has the form:

"DSN=data source name[;attribute=value[;attribute=value]...]"

An example of a connection string for InterBase is:

"DSN=ACCOUNTING;DB=MYSERVER:/usr/interbase/employee.gdb;

UID=FRED;PWD=FREDPASS"


InterBase: Attributes
Attributes

The following table lists the long and short names for each attribute, as well as a description.

Attribute	Description

DataSourceName (DSN)	A string that identifies a single connection to an InterBase database system. Examples include Accounting or InterBase-Serv1.

Database (DB)	The database name to which you want to connect.

LogonID (UID)	The user name used to connect to your InterBase database system.

BinarySegmentSize (BSS)	Segment size used to create a binary BLOB. The size of the segment determines how many bytes the segment can hold. This value does not reflect the maximum size of the BLOB.

CharSegmentSize (CSS)	Segment size used to create a text BLOB. BLOB data are stored in segments. The size of the segment determines how many bytes the segment can hold. This value does not reflect the maximum size of the BLOB.

Note: Blob setting of segment sizes can only be entered manually by editing your ODBC.INI file.


Transaction Isolation Levels

The InterBase ODBC driver supports three transaction types: READ COMMITTED, SNAPSHOT, and SNAPSHOT TABLE STABILITY. The default is READ COMMITTED.

ODBC and InterBase Transaction Levels

ODBC Model	InterBase Model

SQL_TXN_READ_ UNCOMMITTED	READ UNCOMMITTED isolation level. Not supported. InterBase does not provide any way of doing dirty reads.
SQL_TXN_READ_ COMMITTED. No dirty reads, phantom reads, or non-repeatable reads are allowed.	READ COMMITTED isolation level (default). Both types of Read Committed (NO RECORD_VERSION and RECORD_VERSION) provide the properties required of SQL_TXN_READ_COMMITTED.
SQL_TXN_SERIALIZABLE Serializable transactions. Dirty reads, non-repeatable reads, and phantoms are not possible.	SNAPSHOT TABLE STABILITY isolation level. Transactions that reserve table-level locks at start of transaction are serializable. SNAPSHOT TABLE STABILITY does not have dirty reads, non-repeatable reads, or phantoms.
SQL_TXN_VERSIONING transactions are serializable and provide more concurrency than  SQL_TXN_SERIALIZABLE	SNAPSHOT isolation level. Transactions reserve tables (placing shared read/write locks) on the required tables. This does not allow serializability, but provides benefits of versioning, and no dirty reads.


InterBase: Data Types
Data Types

The following table shows how InterBase data types are mapped to the standard ODBC data types:

InterBase	ODBC

BLOB (SegmentSize, 0)	SQL_LONGVARBINARY

BLOB (SegmentSize, 1)	SQL_LONGVARCHAR

CHAR (length)	SQL_CHAR

DATE	SQL_DATE

DOUBLE PRECISION	SQL_DOUBLE

FLOAT	SQL_FLOAT

INTEGER	SQL_INTEGER

SMALLINT	SQL_SMALLINT

VARCHAR (max_length)	SQL_VARCHAR

Note:	BLOB data types cannot be used in a WHERE clause and cannot be inserted into a column as a string. Arrays are not supported.


InterBase Database Names

Whenever you specify an InterBase database name, it must have the following syntax:

server_name <protocol_specifier>\path\database_file

For connecting to any database server with TCP/IP, the protocol specifier is a colon (:) between the server and database names. For example, if SVR1 is an InterBase for NetWare server and you want to connect using TCP/IP:

SVR1:VOL2:\USERS\FRED\MYDB.GDB

For connecting to a NetWare server with Novell SPX, the protocol specifier is an at-sign (@) between the server and database names, and disk volumes must be specified explicitly (do not use drive letter mappings). For example if SVR2 is an InterBase NetWare server:

SVR2@VOL2:\USERS\FRED\MYDB.GDB

For connecting to a Windows NT server with NetBEUI/Named Pipes, the protocol specifier is a double backslash (\\) preceding the server name. Include the explicit disk drive designation and separate disk, directory, and file names with slashes or backslashes. For example if SVR3 is an InterBase NT server:

\\SVR3\D:\USERS\FRED\MYDB.GDB


Using Microsoft Access and Visual Basic
Using Microsoft Access or Visual Basic

Inserts and Deletes

The default transaction model in the InterBase ODBC driver is READ COMMITTED.  This means that when one user makes changes to the database and performs a COMMIT, other users will see the changes immediately.  However, Microsoft Access does not make inserts visible to other users.

Updates

Only changes to non-primary key columns are made visible to other users.  They will have to use the RECORD | REFRESH option or set the ODBC refresh interval to a low value to see the changes.

Updates to primary keys made by a user will not be visible to other users that are viewing the same table(s).  When users refresh their tables, Access displays "#Delete" in place of that row to indicate that the primary key for that row has been changed.  Other users will need to close and re-open the tables to see the new primary key(s).

Multiple connections

Due to connection sharing in Microsoft Access, once a user establishes a connection to a server with a certain user name and password, the user will retain the same user name, even if he uses attached tables with different user names and passwords stored in them.

If you need varying levels of security on multiple tables, make sure that the first user name and password is the desired security privilege for all subsequent connections.

Transactions

Changing the default transaction from READ COMMITTED to SNAPSHOT is up to the ODBC application.  While it is possible to specify your transaction model of choice using Visual Basic, Access does not allow you to do so.

Stored Procedures

ODBC supports InterBase stored procedures.  If a procedure has input only but no output, then the procedure is an EXECUTE procedure, called through ODBC SQLExecDirect.  If the procedure has input and output or output only, then the procedure is a SELECT procedure.  For more information on InterBase Stored Procedures, please see Chapter 3 of the Language Reference manual.  For information on ODBC SQLProcedures, SQLProcedureColumns, and SQLExecDirect please see the ODBC 2.0 SDK Reference.

Computed Columns in Visual Basic

Computed columns are read-only.  Any updates to computed columns will result in an error.  In Visual Basic, you can specify a SELECT statement that excludes the computed column.  Recordsets can then be updated using  Recordset.Update.

Unsupported features

ODBC does not support InterBase event alerters.

ODBC does not support InterBase array datatypes.  You can define a view on a table that contains array datatypes so it can be used in forms and datasheets.  Attempts to select an array column will display "#Name?" for all columns in the table.